How to use VB.Net to print Invoice

Introduction

This is a trial to print Invoice with VB.Net, this project lets you know:

• How to create a report using PrintPreviewDialog control and PrintDocument control?
• How to draw Invoice head?
• How to draw the table of products and its price?
• How to compute and draw Invoice total?

You can read my article about How to use C# to print Invoice.



My Project has three Forms:

1. frmInvoice: to bind DataGrid with all Orders from Northwind database file.
2. frmInput: to choose one Order which you want to print its Invoice.
3. frmOrder: to display Invoice on DataGrid, then you can Print Preview or Print the Invoice as Report.

Also, we need three classes for printing:

System.Windows.Forms.PrintDialog, System.Windows.Forms.PrintPreviewDialog and System.Drawing.Printing.PrintDocument.

Of course you can use any database file instead Northwind.mdb and change my code to connect with your database file, also you can change my SQL string to bind DataGrid with data.

About the Code

Bind the DataGrid in frmInvoice form with all Orders:

' following lines to connect with access database file 'Northwind.mdb'
Dim MyPass As String = ""
Dim MyDataFile As String = Application.StartupPath & "\DataFile\Northwind.mdb"
Dim strCon As String = "provider=microsoft.jet.oledb.4.0;data source=" _
& MyDataFile & ";" & "Jet OLEDB:Database Password=" & MyPass & ";"

' If you are using SQL Server, please replace previous lines with following:
 Dim strCon As String = "provider=sqloledb;Data Source=PC;Initial Catalog=" _
 & "Northwind;Integrated Security=SSPI" & ";"
' and replace 'Data Source=PC' with the name of your system
Try
   ' Get data from tables: Orders, Customers, Employees, Products, Order Details:
   Dim InvSql As String = "SELECT Customers.CompanyName, Customers.City, " _
   & "Employees.FirstName + Space(1) + Employees.LastName AS Salesperson, " _
   & "Orders.OrderID, Orders.OrderDate, " _
   & "[Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, " _
   & "[Order Details].Quantity, [Order Details].Discount, " _
   & "CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, " _
   & "Orders.Freight " _
   & "FROM Products INNER JOIN ((Employees INNER JOIN " _
   & "(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) " _
   & "ON Employees.EmployeeID = Orders.EmployeeID) " _
   & "INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) " _
   & "ON Products.ProductID = [Order Details].ProductID;"
   ' create an OleDbDataAdapter
   Dim datAdp As OleDbDataAdapter = New OleDbDataAdapter(InvSql, strCon)
   ' create a command builder
   Dim cBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(datAdp)
   ' create a DataTable to hold the query results
   Dim dTable As DataTable = New DataTable
   ' fill the DataTable
   datAdp.Fill(dTable)
   ' set DataSource of DataGrid
   datGrid.DataSource = dTable
Catch ex As Exception
   MessageBox.Show(ex.ToString())
End Try

Bind the DataGrid in frmOrder form with one Order:
'InvoiceOrder is the number of Order which you select:
Dim intOrder As Integer = Int32.Parse(InvoiceOrder)
Dim MyDataFile As String = Application.StartupPath & "\DataFile\Northwind.mdb"
Dim MyPass As String = ""
Dim strCon As String = "provider=microsoft.jet.oledb.4.0;data source=" _
& MyDataFile & ";" & "Jet OLEDB:Database Password=" & MyPass & ";"
Try
   ' Get Invoice Data:
   InvSql = "SELECT [Order Details].ProductID, " _
   & "Products.ProductName, [Order Details].UnitPrice, " _
   & "[Order Details].Quantity, [Order Details].Discount, " _
   & "CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 " _
   & "AS ExtendedPrice " _
   & "FROM Products INNER JOIN [Order Details] " _
   & "ON Products.ProductID=[Order Details].ProductID " _
   & "WHERE [Order Details].OrderID = " & intOrder
   ' create an OleDbDataAdapter
   Dim datAdp As OleDbDataAdapter = New OleDbDataAdapter(InvSql, strCon)
   ' create a command builder
   Dim cBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(datAdp)
   ' create a DataTable to hold the query results
   Dim dTable As DataTable = New DataTable
   ' fill the DataTable
   datAdp.Fill(dTable)
   ' Create a TableStyle to format Datagrid columns.
   ordGrid.TableStyles.Clear()
   Dim tableStyle As DataGridTableStyle = New DataGridTableStyle
   For Each dc As DataColumn In dTable.Columns
      Dim txtColumn As DataGridTextBoxColumn = New DataGridTextBoxColumn
      txtColumn.MappingName = dc.ColumnName
      txtColumn.HeaderText = dc.Caption
      Select Case (dc.ColumnName.ToString())
         Case "ProductID" ' Product ID
            txtColumn.HeaderText = "Product ID"
            txtColumn.Width = 60
         Case "ProductName" ' Product Name
            txtColumn.HeaderText = "Product Name"
            txtColumn.Width = 110
         Case "UnitPrice" ' Unit Price
            txtColumn.HeaderText = "Unit Price"
            txtColumn.Format = "0.00"
            txtColumn.Alignment = HorizontalAlignment.Right
            txtColumn.Width = 60
         Case "Discount" ' Discount
            txtColumn.HeaderText = "Discount"
            txtColumn.Format = "p" 'percent
            txtColumn.Alignment = HorizontalAlignment.Right
            txtColumn.Width = 60
         Case "Quantity" ' Quantity
            txtColumn.HeaderText = "Quantity"
            txtColumn.Alignment = HorizontalAlignment.Right
             txtColumn.Width = 50
         Case "ExtendedPrice" ' Extended Price
            txtColumn.HeaderText = "Extended Price"
            txtColumn.Format = "0.00"
            txtColumn.Alignment = HorizontalAlignment.Right
            txtColumn.Width = 90
      End Select
      tableStyle.GridColumnStyles.Add(txtColumn)
   Next
   tableStyle.MappingName = dTable.TableName
   ordGrid.TableStyles.Add(tableStyle)
   ' set DataSource of DataGrid
   ordGrid.DataSource = dTable.DefaultView
Catch ex As Exception
   MessageBox.Show(ex.ToString())
End Try

Declare and initialize three instances for printing:\
Private prnDialog As System.Windows.Forms.PrintDialog
Private prnPreview As System.Windows.Forms.PrintPreviewDialog
Private prnDocument As System.Drawing.Printing.PrintDocument
Me.prnDialog = New System.Windows.Forms.PrintDialog
Me.prnPreview = New System.Windows.Forms.PrintPreviewDialog
Me.prnDocument = New System.Drawing.Printing.PrintDocument
' the Event of 'PrintPage'
AddHandler prnDocument.PrintPage, AddressOf prnDocument_PrintPage

To draw something on the report (as line or text):

1. Get Left Margin, Right Margin, Top Margin, Bottom Margin, Report Width and Report Height:

' Result of the Event 'PrintPage'
Private Sub prnDocument_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs)
    leftMargin = Convert.ToInt32(e.MarginBounds.Left) ' leftMargin, rightMargin, ... Declared before
    rightMargin = Convert.ToInt32(e.MarginBounds.Right)
    topMargin = Convert.ToInt32(e.MarginBounds.Top)
    bottomMargin = Convert.ToInt32(e.MarginBounds.Bottom)
    InvoiceWidth = Convert.ToInt32(e.MarginBounds.Width)
    InvoiceHeight = Convert.ToInt32(e.MarginBounds.Height)
    ' Draw Invoice Head
    SetInvoiceHead(e.Graphics)
End Sub

2. Set Font and Color:

Dim InvTitleFont As Font = New Font("Arial", 24, FontStyle.Regular)
Dim HeadBrush As SolidBrush = New SolidBrush(Color.Blue)

3. Set Font Height and Font Width and coordinate then use DrawString method:

Private Sub SetInvoiceHead(ByVal g As Graphics)
    'Invoice title:
    Dim InvTitle As String = "International Food Company"
    'Title Font:
    Dim InvTitleFont As Font = New Font("Arial", 24, FontStyle.Regular)
    'Title Color:
    Dim HeadBrush As SolidBrush = New SolidBrush(Color.Blue)
    'Title Height:
    Dim InvTitleHeight As Integer = Convert.ToInt32(InvTitleFont.GetHeight(g))
    'Title Length:
    Dim lenInvTitle As Integer = Convert.ToInt32(g.MeasureString(InvTitle, InvTitleFont).Width)
    'Coordinate:
    Dim CurrentX As Integer = leftMargin + (InvoiceWidth - lenInvTitle) / 2 'to set the title in center
    Dim CurrentY As Integer = topMargin + InvTitleHeight
    'draw the title:
    g.DrawString(InvTitle, InvTitleFont, HeadBrush, CurrentX, CurrentY)
End Sub

The project has several pieces of code in three forms, please read the code then run the program to see the result.

If you have any idea or if you find any problems please tell me.